package com.example.demo.mapper;

import com.example.demo.model.UserInfo;
import org.apache.ibatis.annotations.*;

import java.util.List;
//数据库查询
@Mapper
public interface UserInfoMapper {
    //返回查询结果

    @Select("select * from userinfo")
    List<UserInfo> quaryUserInfo();
    //返回类型如果不匹配,会报错
    /**
     * 参数传递
     */

    @Select("select * from userinfo where id=#{id2}")//只有一个参数时,参数名称可以任意

    UserInfo quaryUserInfo2(Integer id3);//只有一个参数时,参数名称可以任意


    @Select("select * from userinfo where id=#{param1} and username=#{param2}")
    //SQL语句：@Select注解中的值是一个SQL查询语句，它将被MyBatis执行以获取数据库中的数据。

    //参数绑定：在SQL语句中，可以使用#{}来作为参数占位符。
    // MyBatis会将方法的参数绑定到这些占位符上。
    // 例如，#{id}和#{userName}分别对应方法中传递的id和userName参数。
    UserInfo quaryUserInfoByIdAndName(Integer id2,String userName);
    //@Select 参数名称和该方法参数名称必须一致,或者通过注解参数重命名

    @Select("select * from userinfo where id=#{id2} and username=#{name2}")
    UserInfo  quaryUserInfoByIdAndName2(@Param("id2") Integer id, @Param("name2") String userName);

    /**
     * 增
     */
    @Insert("insert into userinfo (username ,`password`,age,gender,phone)"  //必须和表的表名相同
            +
            " values(#{username},#{password}," +
            "#{age},#{gender},#{phone})")
    Integer insert(UserInfo userInfo);  //和实体类的属性相同
    @Insert("insert into userinfo (username ,`password`,age,gender,phone,delete_flag)" +
            " values(#{username},#{password}," +
            "#{age},#{gender},#{phone},#{deleteFlag})")
    Integer insert2(UserInfo userInfo);


    @Insert("insert into userinfo (username,`password`,age,gender,id) " +
            "values(#{username},#{password},#{age},#{gender},#{id})")
   Integer insert3(UserInfo userInfo);
    @Insert("insert into userinfo (username,`password`,age,gender,id) " +
            "values(#{userinfo.username},#{userinfo.password},#{userinfo.age}," +
            "#{userinfo.gender},#{userinfo.id})")
    Integer insert3_1(@Param("userinfo") UserInfo userInfo);
    /**
     * 获取自增id
     */
    @Options(useGeneratedKeys = true,keyProperty = "id")
    @Insert("insert into userinfo (username,`password`,age,gender) " +
            "values(#{username},#{password},#{age},#{gender})")
    Integer insert4(UserInfo userInfo);
    @Options(useGeneratedKeys = true,keyProperty = "id")
    @Insert("insert into userinfo (username,`password`,age,gender) " +
            "values(#{userinfo.username},#{userinfo.password},#{userinfo.age},#{userinfo.gender})")
    Integer insert4_1(@Param("userinfo") UserInfo userInfo);


    /**
     * 删
     */
    @Delete("delete from userinfo where id=#{id}")
    Integer delete(Integer id);

    /**
     * 改
     */
    @Update("update userinfo set `password` =#{password} " +
            "where id=#{id}")
    Integer update(UserInfo userInfo);


//    //起别名
//    @Select("select id, username, password, age, gender, phone," +
//            " delete_flag as deleteFlag, create_time as createTime, " +
//            "update_time  as updateTime from userinfo")
//    List<UserInfo> quaryUserInfo4();
//    //注解
//    @Select("select * from userinfo")
//    List<UserInfo> quary();
    /**
     *  有些字段查询结果为null
     *  解决方法:
     *          1. 起别名
     */
    @Select("select id, username, password, age, gender, phone," +
            " delete_flag as deleteFlag, create_time as createTime, " +
            "update_time as updateTime from userinfo")
    List<UserInfo> quary();



    /**
     * 2. 注释
     */

    @Results(id="BaseMap",value = {
            @Result(column = "delete_flag", property ="deleteFlag" ),
            @Result(column = "create_time", property ="createTime" ),
            @Result(column = "update_time", property ="updateTime" )
    })
    @Select("select * from userinfo")
    List<UserInfo> quary2();

    @Select("select * from userinfo")
//    @ResultMap("BaseMap")
    List<UserInfo> quary3();

    /**
     * 配置驼峰自动转换
     */
    @Select("select * from userinfo")
//    @ResultMap("BaseMap")
    List<UserInfo> quary4();


    /**
     * 排序
     */
    @Select("select * from userinfo order by id ${order} ")
    List<UserInfo> qureyInfoByOrder(String order);

    /**
     * 模糊查询
     */
    @Select("select * from userinfo where username like CONCAT('%',#{name},'%')")
    List<UserInfo> qureyInfoByLike(String name);

    /**
     * 动态sql
     */

    @Select(" <script> insert into userinfo (username,`password`,\n" +
            "        <if test=\"age !=null\">" +
            "            age," +
            "        </if>" +
            "        gender) values" +
            "        (#{username},#{password}," +
            "        <if test=\"age!=null\">" +
            "            #{age}," +
            "        </if>" +
            "        #{gender}) </script>"
    )
    Integer insertByconition(UserInfo userInfo);

}



